using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;


namespace EOD
{
    using NUnit.Framework;

    [TestFixture]
    public class GetData
    {
        [Test]
        public void DownloadYahoo()
        {
            libEOD.Web2File w2f = new libEOD.Web2File();
            string sOK = w2f.GetPage("http://www.yahoo.com");
            StringAssert.IsMatch("OK",sOK);
            Console.WriteLine("File {0} created", w2f.FileName);
            FileStream fs = new FileStream(w2f.FileName, FileMode.Open, FileAccess.Read);
            StreamReader sr = new StreamReader(fs);
            string sLine = string.Empty;
            while ( (sLine = sr.ReadLine()) != null) Console.WriteLine(sLine);
            sr.Close();
            fs.Close();
            File.Delete(w2f.FileName);
        }

        [Test]
        public void DownloadWSJ()
        {
            libEOD.WsjUST ust = new libEOD.WsjUST();
            string sOK = ust.GetEOD(DateTime.Today.AddDays(-1));
            StringAssert.IsMatch("OK", sOK);
            Assert.Greater(ust.Notes.Rows.Count, 0);
            Assert.Greater(ust.Bills.Rows.Count, 0);

            //notes.Columns.Add("Cusip");
            //foreach (DataRow row in notes.Rows)
            //{
            //    DateTime dtMat = DateTime.Parse(row["Maturity"].ToString());
            //    double dCoupon = double.Parse(row["Coupon"].ToString());
            //    string sCusip = tsy.GetCusip(dtMat, dCoupon);
            //    if (sCusip != null && sCusip != string.Empty)
            //    {
            //        row["Cusip"] = tsy.GetCusip(dtMat, dCoupon);
            //        Dictionary<string,object> info = tsy.GetSecInfoByCusip(sCusip);
            //        Console.WriteLine("{0}: {1} {2} {3} {4}",
            //        sCusip, dtMat.ToShortDateString(), dCoupon,
            //        info["FistCpnDate"], info["IssueDate"]);
            //    }
            //}
        }

        [Test]
        public void GetAllBonds()
        {
            libEOD.WsjUST ust = new libEOD.WsjUST();
            libRDS.TreasuryDirect td = new libRDS.TreasuryDirect();
            string sOK = ust.GetEOD(DateTime.Today.AddDays(-1));
            StringAssert.IsMatch("OK", sOK);
            string sCusip = string.Empty;
            foreach (DataRow row in ust.Notes.Rows)
            {
                DateTime dtMat = DateTime.Parse(row["Maturity"].ToString());
                double dCoupon = double.Parse(row["Coupon"].ToString());
                //if (dCoupon < 9) continue;
                Console.Write("Looking for {0} , {1}", dtMat.ToShortDateString(), dCoupon);
                sCusip = td.FindCusip(dtMat,dCoupon);
                Console.WriteLine(" Found: {0}", sCusip);
                Assert.Greater(sCusip.Length, 0);
            }
        }
        [Test]
        public void GetStaticData()
        {
            libEOD.WsjUST ust = new libEOD.WsjUST();
            libRDS.TreasuryDirect td = new libRDS.TreasuryDirect();
            string sOK = ust.GetEOD(DateTime.Today.AddDays(-1));
            StringAssert.IsMatch("OK", sOK);
            string sCusip = string.Empty;
            foreach (DataRow row in ust.Notes.Rows)
            {
                DateTime dtMat = DateTime.Parse(row["Maturity"].ToString());
                double dCoupon = double.Parse(row["Coupon"].ToString());
                //if (dCoupon < 9) continue;
                //Console.Write("Looking for {0} , {1}", dtMat.ToShortDateString(), dCoupon);
                sCusip = td.FindCusip(dtMat, dCoupon);
                Console.WriteLine(" Found: {0}", sCusip);
                Assert.Greater(sCusip.Length, 0);
                Dictionary<string,object> data=td.GetSecInfoByCusip(sCusip);
                Console.WriteLine(" INFO: {0} {1} {2}", 
                    sCusip,
                    data["Coupon"].ToString(),
                    data["MaturityDate"].ToString());
                Assert.Greater(data["SecType"].ToString().Length, 0);
            }
        }
        [Test]
        public void InsertAllStatic()
        {
            string sFMT = 
           " INSERT INTO [Sec].[dbo].[Treasuries]"+
           " ([id]" +
           " ,[id_type]" +
           " ,[sec_type]" +
           " ,[short_name]" +
           " ,[description]" +
           " ,[coupon]" +
           " ,[maturity_date]" +
           " ,[issue_date]" +
           " ,[auction_date]" +
           " ,[dated_date]" +
           " ,[first_coupon_date]" +
           " ,[call_date]" +
           " ,[last_trade_date]" +
           " ,[expiration_date]" +
           " ,[auction_volume]" +
           " ,[total_volume])" +
           " VALUES"+
           " ('{0}'" +      //id, varchar(50),>
           ",'{1}'" +       //id_type, varchar(50),>
           ",'{2}'" +       //sec_type, varchar(50),>
           ",'{3}'" +       //short_name, varchar(50),>
           ",'{4}'" +       //description, varchar(50),>
           ",{5}" +       //coupon, float,>
           ",'{6}'" +       //maturity_date, datetime,>
           ",'{7}'" +       //issue_date, datetime,>
           ",'{8}'" +       //auction_date, datetime,>
           ",'{9}'" +       //dated_date, datetime,>
           ",'{10}'" +      //first_coupon_date, datetime,>
           ",'{11}'" +       //call_date, datetime,>
           ",'{12}'" +       //last_trade_date, datetime,>
           ",'{13}'" +       //expiration_date, datetime,>
           ",{14}" +       //auction_volume, int,>
           ",{15})";       //total_volume, int,>)

            string sConnStr = string.Format("Data Source={0};Initial Catalog={1};integrated security=SSPI;",
                "SERGEY-LP\\SQLDEVLP", "Sec");
            SqlConnection conn = new SqlConnection(sConnStr);
            conn.Open();


            libEOD.WsjUST ust = new libEOD.WsjUST();
            libRDS.TreasuryDirect td = new libRDS.TreasuryDirect();
            string sOK = ust.GetEOD(DateTime.Today.AddDays(-1));
            StringAssert.IsMatch("OK", sOK);
            string sCusip = string.Empty;
            foreach (DataRow row in ust.Notes.Rows)
            {
                DateTime dtMat = DateTime.Parse(row["Maturity"].ToString());
                double dCoupon = double.Parse(row["Coupon"].ToString());
                //if (dCoupon < 9) continue;
                //Console.Write("Looking for {0} , {1}", dtMat.ToShortDateString(), dCoupon);
                sCusip = td.FindCusip(dtMat, dCoupon);
                Console.WriteLine(" Found: {0}", sCusip);
                Assert.Greater(sCusip.Length, 0);
                Dictionary<string, object> data = td.GetSecInfoByCusip(sCusip);
                string sSQL = fnMakeSQL(sCusip,data, sFMT);
                fnExecuteSQL(conn,sSQL);
                Console.WriteLine(" INFO: {0} {1} {2}",
                    sCusip,
                    data["Coupon"].ToString(),
                    data["MaturityDate"].ToString());
                Assert.Greater(data["SecType"].ToString().Length, 0);
            }
            conn.Close();
            conn.Dispose();
        }

        void fnExecuteSQL(SqlConnection conn,string sSQL)
        {
            SqlCommand cmd = new SqlCommand(sSQL, conn);
            int nRet = cmd.ExecuteNonQuery();
            cmd.Dispose();
        }

        string fnMakeSQL(string sCusip,Dictionary<string,object>data,string sFMT)
        {
            return string.Format(sFMT,
                sCusip,//id, varchar(50),>
                "cusip",//id_type, varchar(50),>
                data["SecType"].ToString(),//sec_type, varchar(50),>
                "NA",//short_name, varchar(50),>
                "NA",//description, varchar(50),>
                data["Coupon"],//coupon, float,>
                data["MaturityDate"],//maturity_date, datetime,>
                data["IssueDate"],//issue_date, datetime,>
                data["AuctionDate"],//auction_date, datetime,>
                data["IssueDate"],//dated_date, datetime,>
                data["FistCpnDate"],//first_coupon_date, datetime,>
                "",  //call_date, datetime,>
                "", //last_trade_date, datetime,>
                "", //expiration_date, datetime,>
                data["TotalAccepted"], //auction_volume, int,>
                data["TotalAccepted"]); //total_volume, int,>)
        }
        [Test]
        public void SaveEOD()
        {
            DateTime dt = DateTime.Today.AddDays(-2);
            libEOD.WsjUST ust = new libEOD.WsjUST();
            string sOK = ust.GetEOD(dt);
            StringAssert.IsMatch("OK", sOK);
            bool bOK = ust.Save();
            Assert.IsTrue(bOK);
        }
    }
}
